﻿Imports System.Data.OleDb
Public Class report
    Dim cmd As OleDbCommand
    Dim con As OleDbConnection
    Dim dr As OleDbDataReader
    Dim dr1 As OleDbDataReader
    Dim a As Integer
    Dim pre As Double
    Dim totaldebit As Double
    Dim totalcredit As Double
    Dim p As String
    Dim rn As Long
    Public Sub fillreport(ByVal id As Integer, ByVal qry As String, ByVal dfrom As Date, ByVal dto As Date, ByVal party As String)
        Try
            Dim str As String, i As Integer = 1
            Dim d As Double
            str = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & Application.StartupPath & "/ladger.accdb;"
            con = New OleDbConnection(str)
            con.Open()
            Me.Show()
            p = party
            RichTextBox1.Text = RichTextBox1.Text & "<html>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<center><head>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<TITLE> REPORT </title>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<style type=" & """" & "text/css" & """" & " > " & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "@import url(" & """" & "style.css" & """" & ");" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</style>"
            RichTextBox1.Text = RichTextBox1.Text & "</head></center>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<body>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<table  border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<td>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "Ledger" & "<br>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</td>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<table  border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<td>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<b>" & Today.Date.ToString("dd/MMM/yyyy") & "</b><br>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</td>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf

            RichTextBox1.Text = RichTextBox1.Text & "<b>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<table  border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Party: " & party & "</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<table border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Date " & Format(dfrom, "dd/MMM/yyyy") & " - " & Format(dto, "dd/MMM/yyyy") & "</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</b>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<hr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<table border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Date</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Narration</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Credit</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Debit</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<th>Balance</th>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
            Dim str1 As String, ob As Object
            str1 = "select sum(camount)-sum(damount) from ladger where accountid=" & id & " and billdate<#" & Format(dfrom, "dd/MMM/yyyy") & "#"
            cmd = New OleDbCommand(str1, con)
            ob = cmd.ExecuteScalar

            If ob.ToString = "" Then
                totalcredit = 0
            Else
                RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf

                If Val(ob.ToString) < 0 Then
                    totaldebit = Val(ob.ToString)
                    RichTextBox1.Text = RichTextBox1.Text & "<td>" & Format(dfrom, "dd/MMM/yyyy") & "</td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td>Previous credit balance</td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totaldebit & "</td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totaldebit & "</td>" & vbCrLf
                    pre = totaldebit
                Else
                    totalcredit = Val(ob.ToString)
                    RichTextBox1.Text = RichTextBox1.Text & "<td>" & Format(dfrom, "dd/MMM/yyyy") & "</td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td>Previous credit balance</td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totalcredit & "</td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totalcredit & "</td>" & vbCrLf
                    pre = totalcredit
                End If
                RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                i = i + 1
            End If
            cmd = New OleDbCommand(qry, con)
            dr = cmd.ExecuteReader
            While dr.Read
                RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
                RichTextBox1.Text = RichTextBox1.Text & "<td>" & Format(dr(0), "dd/MMM/yyyy") & "</td>" & vbCrLf
                RichTextBox1.Text = RichTextBox1.Text & "<td>" & dr(1).ToString & "</td>" & vbCrLf
                d = Val(dr(3).ToString)
                totalcredit = d
                RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & d.ToString("#0.00") & "</td>" & vbCrLf
                d = 0
                d = Val(dr(2).ToString)
                totaldebit = d
                RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & d.ToString("#0.00") & "</td>" & vbCrLf
                d = 0
                If i = 0 Then
                    RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & Val(totalcredit - totaldebit).ToString("#0.00") & "</td>" & vbCrLf
                    pre = Val(totalcredit - totaldebit)
                Else
                    RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & Val((pre + totalcredit) - totaldebit).ToString("#0.00") & "</td>" & vbCrLf
                    pre = (pre + totalcredit) - totaldebit
                End If
                RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
            End While
            RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<br>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<b>Closing Balance:</b> " & pre.ToString("#0.00") & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</body>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</html>"
       
            RichTextBox1.SaveFile(p & ".htm", RichTextBoxStreamType.PlainText)
            WebBrowser1.Navigate(Application.StartupPath & "\" & p & ".htm")
            RichTextBox1.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        WebBrowser1.ShowPrintPreviewDialog()
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        WebBrowser1.ShowPageSetupDialog()
    End Sub
    Private Sub report_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Try
            My.Computer.FileSystem.DeleteFile(p & ".htm")
            My.Computer.FileSystem.DeleteFile("closing.htm")
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub
    Public Sub reportclosing(ByVal l() As String, ByVal j As Integer, ByVal bsel() As Boolean)
        Try
            Dim i As Integer
            Dim credit As Double
            Dim debit As Double
            Dim str As String
            str = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & Application.StartupPath & "/ladger.accdb;"
            con = New OleDbConnection(str)
            con.Open()
            Me.Show()
            RichTextBox1.Text = RichTextBox1.Text & "<html>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<center><head>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<TITLE> REPORT </title>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<style type=" & """" & "text/css" & """" & " > " & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "@import url(" & """" & "style.css" & """" & ");" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</style>"
            RichTextBox1.Text = RichTextBox1.Text & "</head></center>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<body>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<h3>Closing Balance" & "</h3><br>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "<b>" & vbCrLf
            While i < j
                If bsel(i) = False Then
                    RichTextBox1.Text = RichTextBox1.Text & "<table border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Party</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Credit</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Debit</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "</b>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<td>" & l(i) & "</td>" & vbCrLf
                    str = " select sum(camount)-sum(damount) as bal from ladger where accountid=(select customer_id from customer_master where customer_name='" & l(i) & "')"
                    cmd = New OleDbCommand(str, con)
                    dr = cmd.ExecuteReader
                    If dr.Read Then
                        If Val(dr(0).ToString) < 0 Then
                            RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                            debit = debit + (-1 * Val(dr(0).ToString))
                            RichTextBox1.Text = RichTextBox1.Text & "<td>" & (-1 * Val(dr(0).ToString)) & "</td>" & vbCrLf
                        Else
                            credit = credit + Val(dr(0).ToString)
                            RichTextBox1.Text = RichTextBox1.Text & "<td>" & dr(0).ToString & "</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                        End If
                    Else
                        RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                        RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                    End If
                    RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
                Else

                    Dim str1 As String, ob As Object, ddd As Date
                    RichTextBox1.Text = RichTextBox1.Text & "<table  border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Party: " & l(i) & "</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<table border=1 id=" & """" & "box-table-b" & """" & ">" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Date</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Narration</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Credit</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Debit</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<th>Balance</th>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                    str1 = "select cdate from consolidate where accountid=(select customer_id from customer_master where customer_name='" & l(i) & "')"
                    cmd = New OleDbCommand(str1, con)
                    dr = cmd.ExecuteReader
                    If dr.Read Then
                        ddd = dr(0)
                    End If
                    If ddd.ToString <> "" Then
                        str1 = "select sum(camount)-sum(damount) from ladger where accountid=(select customer_id from customer_master where customer_name='" & l(i) & "') and billdate<#" & Format(ddd, "dd/MMM/yyyy") & "#"
                        cmd = New OleDbCommand(str1, con)
                        ob = cmd.ExecuteScalar
                    End If
                    If ob.ToString = "" Then
                        totalcredit = 0
                    Else
                        RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf

                        If Val(ob.ToString) < 0 Then
                            totaldebit = Val(ob.ToString)
                            RichTextBox1.Text = RichTextBox1.Text & "<td>" & Format(ddd, "dd/MMM/yyyy") & "</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td>Previous credit balance</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totaldebit & "</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totaldebit & "</td>" & vbCrLf
                            pre = totaldebit
                        Else
                            totalcredit = Val(ob.ToString)
                            RichTextBox1.Text = RichTextBox1.Text & "<td>" & Format(ddd, "dd/MMM/yyyy") & "</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td>Previous credit balance</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totalcredit & "</td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td></td>" & vbCrLf
                            RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & totalcredit & "</td>" & vbCrLf
                            pre = totalcredit
                        End If
                        RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                    End If
                    Dim d As Double
                    If ob.ToString <> "" Then
                        str = "select billdate,narration,damount,camount from ladger where accountid=(select customer_id from customer_master where customer_name='" & l(i) & "') and billdate<#" & Format(ddd, "dd/MMM/yyyy") & "#"
                    Else
                        str = "select billdate,narration,damount,camount from ladger where accountid=(select customer_id from customer_master where customer_name='" & l(i) & "')"
                    End If
                    cmd = New OleDbCommand(str, con)
                    dr = cmd.ExecuteReader
                    Dim k As Integer
                    While dr.Read
                        RichTextBox1.Text = RichTextBox1.Text & "<tr>" & vbCrLf
                        RichTextBox1.Text = RichTextBox1.Text & "<td>" & Format(dr(0), "dd/MMM/yyyy") & "</td>" & vbCrLf
                        RichTextBox1.Text = RichTextBox1.Text & "<td>" & dr(1).ToString & "</td>" & vbCrLf
                        d = Val(dr(3).ToString)
                        totalcredit = d
                        RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & d.ToString("#0.00") & "</td>" & vbCrLf
                        d = 0
                        d = Val(dr(2).ToString)
                        totaldebit = d
                        RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & d.ToString("#0.00") & "</td>" & vbCrLf
                        d = 0
                        If k = 0 Then
                            RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & Val(totalcredit - totaldebit).ToString("#0.00") & "</td>" & vbCrLf
                            pre = Val(totalcredit - totaldebit)
                        Else
                            RichTextBox1.Text = RichTextBox1.Text & "<td align=right>" & Val((pre + totalcredit) - totaldebit).ToString("#0.00") & "</td>" & vbCrLf
                            pre = (pre + totalcredit) - totaldebit
                        End If
                        k = k + 1
                        RichTextBox1.Text = RichTextBox1.Text & "</tr>" & vbCrLf
                    End While
                    RichTextBox1.Text = RichTextBox1.Text & "</table>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<br>" & vbCrLf
                    RichTextBox1.Text = RichTextBox1.Text & "<b>Closing Balance:</b> " & pre.ToString("#0.00") & vbCrLf
                End If
                pre = 0
                totalcredit = 0
                totaldebit = 0
                i = i + 1
            End While
            
            'RichTextBox1.Text = RichTextBox1.Text & "<br>" & vbCrLf
            'RichTextBox1.Text = RichTextBox1.Text & "<br>" & vbCrLf
            'RichTextBox1.Text = RichTextBox1.Text & "<b> Total Credit Amount is " & credit & "<br> Total Debit Amount is " & debit & " </b>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</body>" & vbCrLf
            RichTextBox1.Text = RichTextBox1.Text & "</html>"
      
            RichTextBox1.SaveFile("closing.htm", RichTextBoxStreamType.PlainText)
            WebBrowser1.Navigate(Application.StartupPath & "\" & "closing.htm")
            RichTextBox1.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        WebBrowser1.ShowSaveAsDialog()

    End Sub
End Class